﻿using eLoan.Models;
using iTextSharp.text;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using System.Web.UI.HtmlControls;

namespace eLoan.Controllers
{
    public class FundingController : BaseController
    {
        
        public ActionResult Index()
        {
            try
            {
                return RedirectToAction("Index", "Home");
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult Loans(string branchCode, string searchText = "")
        {
            try
            {

                CheckUserPageAccess(AdministrationHelper.UserPageAccess.Loans);

                var db = new DatabaseContext();
                var loanListModel = new LoanListModel();

                loanListModel.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, User.Identity.Name);
                loanListModel.SelectedBranchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, User.Identity.Name) : branchCode;
                loanListModel.SearchText = searchText;

                var loanItems = FundingHelper.GetLoanList(loanListModel.SelectedBranchCode, loanListModel.SearchText);
                loanListModel.LoanList = FundingHelper.GetLoanListModel(loanItems);

                return View(loanListModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult CancelLoan(string branchCode, string searchText = "")
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.CancelLoan);

                var db = new DatabaseContext();
                var model = FundingHelper.GetCancelLoanModel(db, branchCode, searchText, User.Identity.Name);

                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult CancelLoan(CancelLoanModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.CancelLoan);

                using (var db = new DatabaseContext())
                {
                    if (command == AdministrationHelper.DBCommand.Save.ToString() && model.CancelLoanItems != null)
                    {
                        foreach (var item in model.CancelLoanItems)
                        {
                            if (item.IsSelected)
                            {
                                var loan = db.Loans.Where(w => w.DocumentNo == item.DocumentNo).SingleOrDefault<Loan>();
                                loan.StatusID = (int)AdministrationHelper.LoanStatus.Cancelled;
                                db.Entry(loan).State = System.Data.EntityState.Modified;
                            }
                        }

                        db.SaveChanges();
                    }
                    else
                    {
                        // refreshed
                    }
                }

                return RedirectToAction("CancelLoan", "Funding", new { branchCode = model.SelectedBranchCode, searchText = model.SearchText });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult RefreshLoanList(LoanListModel model)
        {
            return RedirectToAction("Loans", "Funding", new { branchCode = model.SelectedBranchCode, searchText = model.SearchText });
        }

        public ActionResult PrintCheque(string branchCode, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintCheque);

                var db = new DatabaseContext();
                var printChequeModel = FundingHelper.GetPrintChequeModel(db, branchCode, searchText, User.Identity.Name);

                return View(printChequeModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult PrintCheque(PrintChequeModel printChequeModel, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintCheque);

                using (var db = new DatabaseContext())
                {
                    if (command == AdministrationHelper.DBCommand.Save.ToString())
                    {
                        foreach (var model in printChequeModel.PrintChequeItems)
                        {
                            if (model.IsSelected)
                            {
                                db.PrintedCheques.Add(new PrintedCheque() 
                                                        { 
                                                            DocumentNo = model.DocumentNo,
                                                            BankID = model.BankID,
                                                            DatePrinted = DateTime.Now,
                                                            ChequeNo = model.ChequeNo,
                                                            ChequeAmount = model.ChequeAmount,
                                                            IsVoid = false,
                                                            PrintedBy = User.Identity.Name
                                                        });
                            }
                        }

                        db.SaveChanges();
                    }
                    else
                    {
                        // refreshed
                    }
                }

                return RedirectToAction("PrintCheque", "Funding", new { branchCode = printChequeModel.SelectedBranchCode, searchText = printChequeModel.SearchText });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintChequeToPDF(string documentNo, string branchCode)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintCheque);

                var table = GetChequeTable(documentNo);
                var html = ReportHelper.GetHtmlString(table);
                var left = PageSize.A4.Left;
                var right = PageSize.A4.Right;
                var top = PageSize.A4.Top / 4;
                var bottom = PageSize.A4.Bottom;
                var pageSize = new iTextSharp.text.Rectangle(right, top);

                string fileName = @"Cheque_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                string path = ServerAdminReportsPath + fileName;

                ReportHelper.ExportToPDF(html, path, PageSize.LETTER, marginTop:40f, marginLeft:80f, marginRight:90f);
                ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);

                return RedirectToAction("PrintCheque", "Funding", new { branchCode = branchCode });

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintContract(string branchCode, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var db = new DatabaseContext();
                var printContractModel = FundingHelper.GetPrintContractModel(db, User.Identity.Name, branchCode, searchText);
                return View(printContractModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult VoidCheque(string documentNo, string branchCode)
        {
            var printedCheque = new DatabaseContext().PrintedCheques
                .Where(w => w.DocumentNo == documentNo && !w.IsVoid).SingleOrDefault<PrintedCheque>();

            ViewBag.BranchCode = branchCode;
            return View(printedCheque);
        }

        [HttpPost]
        public ActionResult VoidPrintedCheque(string documentNo, string afterSave)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var arr = afterSave.Split(',');

                if (arr[0] == AdministrationHelper.DBCommand.Void.ToString())
                {
                    using (var db = new DatabaseContext())
                    {
                        var printedCheque = db.PrintedCheques
                            .Where(w => w.DocumentNo == documentNo && !w.IsVoid).SingleOrDefault<PrintedCheque>();

                        printedCheque.IsVoid = true;
                        printedCheque.VoidBy = User.Identity.Name;
                        printedCheque.VoidDate = DateTime.Now;
                        db.Entry(printedCheque).State = System.Data.EntityState.Modified;
                        db.SaveChanges();
                    }
                }
                else
                {
                    // cancelled
                }

                return RedirectToAction("PrintContract", "Funding", new { branchCode = arr[1] });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintDisclosure(string documentNo, string branchCode)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var contractModel = FundingHelper.GetContractModel(documentNo);
                string path = System.Configuration.ConfigurationManager.AppSettings["DisclosurePath"].ToString();
                var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + path);

                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    
                    var ws = package.Workbook.Worksheets["DISCLOSURE"];

                    ws.Cells["D5"].Value = contractModel.BorrowerName;
                    ws.Cells["D6"].Value = contractModel.BorrowerAddress;
                    ws.Cells["V8"].Value = "PHP   " + (contractModel.LoanAmount + contractModel.LoanInterestAmount).ToString("#,###,##0.00");
                    ws.Cells["K11"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID) + " which payable in a " + contractModel.ProceedType;
                    ws.Cells["F11"].Value = (contractModel.LoanInterestAmount / (contractModel.LoanAmount + contractModel.LoanInterestAmount) * 100).ToString("##0.00") + " %";
                    ws.Cells["I11"].Value = contractModel.Term.ToString();
                    ws.Cells["T12"].Value = "PHP   " + contractModel.LoanInterestAmount.ToString("#,###,##0.00");

                    var db = new DatabaseContext();
                    var financeChargeIds = db.ChargeTypes.Where(w => w.SortID == (int)AdministrationHelper.ChargeTypeSort.Finance).Select(s => s.ID);
                    var nonFinanceChargeIds = db.ChargeTypes.Where(w => w.SortID == (int)AdministrationHelper.ChargeTypeSort.NonFinance).Select(s => s.ID);

                    var financeCharges = contractModel.Deductions.Where(w => financeChargeIds.Contains(w.ChargeTypeID)).ToList();
                    var nonFinanceCharges = contractModel.Deductions.Where(w => nonFinanceChargeIds.Contains(w.ChargeTypeID)).ToList();

                    char start = 'b';
                    var rowStart = 13;
                    decimal totalFinanceCharges = 0;
                    var strFin = "";
                    foreach(var item in financeCharges)
                    {
                        ws.Cells["C" + rowStart.ToString()].Value = start + ".";
                        ws.Cells["D" + rowStart.ToString()].Value = AdministrationHelper.GetChargeTypeName(item.ChargeTypeID);
                        ws.Cells["T" + rowStart.ToString()].Value = "PHP   " + item.Amount.ToString("#,###,##0.00");

                        strFin = strFin + ", " + start;
                        totalFinanceCharges = totalFinanceCharges + item.Amount;

                        ++start;
                        ++rowStart;
                    }
                    ws.Cells["C10"].Value = "FINANCE CHARGES (Sum of a {0})".Replace("{0}", strFin);
                    ws.Cells["V10"].Value = "PHP  " + (contractModel.LoanInterestAmount + totalFinanceCharges).ToString("#,###,##0.00");

                    start = 'a';
                    rowStart = 17;
                    decimal totalNonFinanceCharges = 0;
                    strFin = "";
                    foreach (var item in nonFinanceCharges)
                    {
                        ws.Cells["C" + rowStart.ToString()].Value = start + ".";
                        ws.Cells["D" + rowStart.ToString()].Value = AdministrationHelper.GetChargeTypeName(item.ChargeTypeID);
                        ws.Cells["T" + rowStart.ToString()].Value = "PHP   " + item.Amount.ToString("#,###,##0.00");

                        strFin = strFin + ", " + start;
                        totalNonFinanceCharges = totalNonFinanceCharges + item.Amount;

                        ++start;
                        ++rowStart;
                    }

                    if (strFin.Length > 0)
                        strFin = strFin.Substring(1, strFin.Length - 1);

                    ws.Cells["C16"].Value = "NON-FINANCE CHARGES (Sum of {0})".Replace("{0}", strFin);
                    ws.Cells["V16"].Value = "PHP  " + totalNonFinanceCharges.ToString("#,###,##0.00");
                    ws.Cells["V24"].Value = "PHP  " + (contractModel.ChequeAmount).ToString("#,###,##0.00");
                    ws.Cells["I27"].Value = contractModel.Term.ToString();
                    ws.Cells["K27"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID) + " on a " + contractModel.ProceedType + " basis";

                    start = 'a';
                    rowStart = 31;
                    foreach (var item in contractModel.Collaterals)
                    {
                        ws.Cells["C" + rowStart.ToString()].Value = start + ".";
                        ws.Cells["D" + rowStart.ToString()].Value = item.MakeOrType;
                        ws.Cells["M" + rowStart.ToString()].Value = item.BrandOrFileNo;
                        ws.Cells["T" + rowStart.ToString()].Value = item.SerialOrPlateNo;

                        ++start;
                        ++rowStart;
                    }

                    ws.Cells["C47"].Value = contractModel.BranchManager;

                    string fileName = @"Disclosure_"  + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                    string reportsPath = ServerAdminReportsPath + fileName;

                    var newFileInfo = new FileInfo(reportsPath);
                    if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                    package.SaveAs(newFileInfo);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("PrintContract", "Funding", new { branchCode = branchCode });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintPNCM(string documentNo, string branchCode)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var contractModel = FundingHelper.GetContractModel(documentNo);
                string path = System.Configuration.ConfigurationManager.AppSettings["PNCMPath"].ToString();
                var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + path);

                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {

                    var ws = package.Workbook.Worksheets["PNCM"];

                    ws.Cells["P4"].Value = contractModel.BranchName;
                    ws.Cells["D7"].Value = contractModel.BranchAddress;
                    ws.Cells["Z7"].Value = contractModel.BranchManager;
                    ws.Cells["B9"].Value = contractModel.BorrowerName;
                    ws.Cells["X9"].Value = contractModel.BorrowerAddress;
                    ws.Cells["B32"].Value = contractModel.BorrowerAddress;
                    ws.Cells["T14"].Value = FundingHelper.ConvertDecimalToWords((contractModel.LoanAmount + contractModel.LoanInterestAmount).ToString());
                    ws.Cells["C15"].Value = "PHP   " + (contractModel.LoanAmount + contractModel.LoanInterestAmount).ToString("#,###,##0.00");
                    ws.Cells["M15"].Value = (contractModel.LoanInterestAmount / (contractModel.LoanAmount + contractModel.LoanInterestAmount) * 100).ToString("##0.00") + " %";
                    ws.Cells["X15"].Value = contractModel.Term.ToString();
                    ws.Cells["Z15"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);
                    ws.Cells["O16"].Value = FundingHelper.ConvertDecimalToWords(contractModel.LoanAmount.ToString());
                    ws.Cells["AF16"].Value = "PHP   " + contractModel.LoanAmount.ToString("#,###,##0.00");
                    ws.Cells["N18"].Value = contractModel.Term.ToString();
                    ws.Cells["P18"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);
                    ws.Cells["S18"].Value = contractModel.ProceedType;

                    // receivableAmount
                    var receivableAmount = contractModel.LoanAmount + contractModel.LoanInterestAmount;

                    // proceed type amount
                    decimal proceedTypeAmount = GetScheduleAmount(receivableAmount, contractModel.Term, contractModel.ProceedType);
                    ws.Cells["AA18"].Value = FundingHelper.ConvertDecimalToWords(proceedTypeAmount.ToString());
                    ws.Cells["C19"].Value = "PHP   " + proceedTypeAmount.ToString("#,###,##0.00");

                    ws.Cells["E20"].Value = contractModel.BranchAddress;
                    
                    var advancedPayment = contractModel.Deductions.Where(w => w.ChargeTypeID == 4);
                    decimal advancedPaymentAmt = advancedPayment.Any() ? advancedPayment.SingleOrDefault<LoanDeduction>().Amount : 0;

                    // daily amount
                    decimal dailyAmount = GetScheduleAmount(receivableAmount, contractModel.Term, AdministrationHelper.ProceedType.Daily.ToString());
                    //ws.Cells["U21"].Value = FundingHelper.ConvertDecimalToWords(dailyAmount.ToString());
                    //ws.Cells["C22"].Value = "PHP   " + dailyAmount.ToString("#,###,##0.00");

                    if (contractModel.CoMakers.Count() >= 1)
                    {
                        ws.Cells["B23"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["Y23"].Value = contractModel.CoMakers[0].Address;
                        ws.Cells["B27"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["U67"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["B82"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["N82"].Value = contractModel.CoMakers[0].IDNo;
                    }

                    if (contractModel.CoMakers.Count() >= 2)
                    {
                        ws.Cells["C24"].Value = contractModel.CoMakers[1].CoMakerName;
                        ws.Cells["Y24"].Value = contractModel.CoMakers[1].Address;
                    }

                    var rowStart = 34;
                    foreach (var item in contractModel.Collaterals)
                    {
                        ws.Cells["B" + rowStart].Value = item.MakeOrType;
                        ws.Cells["M" + rowStart].Value = item.BrandOrFileNo;
                        ws.Cells["X" + rowStart].Value = item.SerialOrPlateNo;

                        ++rowStart;

                        if (rowStart == rowStart + 3)
                            break;
                    }

                    ws.Cells["B62"].Value = contractModel.BranchName;
                    ws.Cells["AB64"].Value = contractModel.BranchName;
                    ws.Cells["C67"].Value = contractModel.BorrowerName;
                    ws.Cells["M73"].Value = contractModel.BranchManager;

                    ws.Cells["C77"].Value = contractModel.BranchLoanProcessor;
                    ws.Cells["U77"].Value = contractModel.BranchTreasurySpecialist;

                    ws.Cells["B81"].Value = contractModel.BorrowerName;
                    ws.Cells["N81"].Value = contractModel.BorrowerID;
                    
                    ws.Cells["B83"].Value = contractModel.BranchManager;
                    ws.Cells["N83"].Value = contractModel.BranchManagerID;

                    string fileName  = @"PNCM_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                    string reportsPath = ServerAdminReportsPath + fileName;
                    var newFileInfo = new FileInfo(reportsPath);
                    if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                    package.SaveAs(newFileInfo);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);

                }

                return RedirectToAction("PrintContract", "Funding", new { branchCode = branchCode });

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintPNRL(string documentNo, string branchCode)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var contractModel = FundingHelper.GetContractModel(documentNo);
                string path = System.Configuration.ConfigurationManager.AppSettings["PNRLPath"].ToString();
                var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + path);

                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    var ws = package.Workbook.Worksheets["PNRL"];

                    ws.Cells["P5"].Value = contractModel.BranchName;
                    ws.Cells["D8"].Value = contractModel.BranchAddress;
                    ws.Cells["Z8"].Value = contractModel.BranchManager;
                    ws.Cells["B10"].Value = contractModel.BorrowerName;
                    ws.Cells["X10"].Value = contractModel.BorrowerAddress;

                    var receivableAmount = contractModel.LoanAmount + contractModel.LoanInterestAmount;
                    ws.Cells["T15"].Value = FundingHelper.ConvertDecimalToWords(receivableAmount.ToString());
                    ws.Cells["C16"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");

                    ws.Cells["M16"].Value = (contractModel.LoanInterestAmount / (contractModel.LoanAmount + contractModel.LoanInterestAmount) * 100).ToString("##0.00") + " %";
                    ws.Cells["X16"].Value = contractModel.Term.ToString();
                    ws.Cells["Z16"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);
                    ws.Cells["O17"].Value = FundingHelper.ConvertDecimalToWords(contractModel.LoanAmount.ToString());
                    ws.Cells["AF17"].Value = "PHP   " + contractModel.LoanAmount.ToString("#,###,##0.00");
                    ws.Cells["N19"].Value = contractModel.Term.ToString();
                    ws.Cells["P19"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);
                    ws.Cells["S19"].Value = contractModel.ProceedType;

                    var proceedAmount = GetScheduleAmount(receivableAmount, contractModel.Term, contractModel.ProceedType);
                    ws.Cells["AA19"].Value = FundingHelper.ConvertDecimalToWords(proceedAmount.ToString());
                    ws.Cells["C20"].Value = "PHP   " + proceedAmount.ToString("#,###,##0.00");
                    ws.Cells["E21"].Value = contractModel.BranchAddress;

                    if (contractModel.CoMakers.Count() >= 1)
                    {
                        ws.Cells["B22"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["B26"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["U47"].Value = contractModel.CoMakers[0].CoMakerName;

                        ws.Cells["Y22"].Value = contractModel.CoMakers[0].Address;
                    }

                    ws.Cells["C47"].Value = contractModel.BorrowerName;
                    ws.Cells["M53"].Value = contractModel.BranchManager;

                    ws.Cells["C57"].Value = contractModel.BranchLoanProcessor;
                    ws.Cells["U57"].Value = contractModel.BranchTreasurySpecialist;

                    string fileName = @"PNRL_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                    string reportsPath = ServerAdminReportsPath + fileName;
                    var newFileInfo = new FileInfo(reportsPath);
                    if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                    package.SaveAs(newFileInfo);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("PrintContract", "Funding", new { branchCode = branchCode });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintPNCQ(string documentNo, string branchCode)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var contractModel = FundingHelper.GetContractModel(documentNo);
                string path = System.Configuration.ConfigurationManager.AppSettings["PNCQPath"].ToString();
                var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + path);

                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    var ws = package.Workbook.Worksheets["PNCQ"];

                    ws.Cells["P4"].Value = contractModel.BranchName;
                    ws.Cells["D7"].Value = contractModel.BranchAddress;
                    ws.Cells["Z7"].Value = contractModel.BranchManager;
                    ws.Cells["B9"].Value = contractModel.BorrowerName;
                    ws.Cells["X9"].Value = contractModel.BorrowerAddress;

                    if (contractModel.CoMakers.Count() >= 1)
                    {
                        ws.Cells["B21"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["B25"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["U46"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["B61"].Value = contractModel.CoMakers[0].CoMakerName;
                        ws.Cells["N61"].Value = contractModel.CoMakers[0].IDNo;

                        ws.Cells["Y21"].Value = contractModel.CoMakers[0].Address;
                    }

                    var receivableAmount = contractModel.LoanAmount + contractModel.LoanInterestAmount;
                    ws.Cells["T14"].Value = FundingHelper.ConvertDecimalToWords(receivableAmount.ToString());
                    ws.Cells["C15"].Value = receivableAmount.ToString("#,###,##0.00");
                    
                    ws.Cells["M15"].Value = (contractModel.LoanInterestAmount / (contractModel.LoanAmount + contractModel.LoanInterestAmount) * 100).ToString("##0.00") + " %";
                    ws.Cells["X15"].Value = contractModel.Term.ToString();
                    ws.Cells["Z15"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);

                    ws.Cells["O16"].Value = FundingHelper.ConvertDecimalToWords(contractModel.LoanAmount.ToString());
                    ws.Cells["AF16"].Value = "PHP   " + contractModel.LoanAmount.ToString("#,###,##0.00");
                    
                    ws.Cells["N18"].Value = contractModel.Term.ToString();
                    ws.Cells["P18"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);
                    ws.Cells["S18"].Value = contractModel.ProceedType;

                    var proceedAmount = GetScheduleAmount(receivableAmount, contractModel.Term, contractModel.ProceedType);
                    ws.Cells["AA18"].Value = FundingHelper.ConvertDecimalToWords(proceedAmount.ToString());
                    ws.Cells["C19"].Value = "PHP   " + proceedAmount.ToString("#,###,##0.00");

                    ws.Cells["B41"].Value = contractModel.BranchName;
                    
                    ws.Cells["AB43"].Value = contractModel.BranchName;
                    ws.Cells["C46"].Value = contractModel.BorrowerName;
                    ws.Cells["M52"].Value = contractModel.BranchManager;

                    ws.Cells["C56"].Value = contractModel.BranchLoanProcessor;
                    ws.Cells["U56"].Value = contractModel.BranchTreasurySpecialist;

                    ws.Cells["B60"].Value = contractModel.BorrowerName;
                    ws.Cells["N60"].Value = contractModel.BorrowerID;

                    ws.Cells["B62"].Value = contractModel.BranchManager;
                    ws.Cells["N62"].Value = contractModel.BranchManagerID;


                    string fileName = @"PNCQ_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                    string reportsPath = ServerAdminReportsPath + fileName;
                    var newFileInfo = new FileInfo(reportsPath);
                    if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                    package.SaveAs(newFileInfo);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("PrintContract", "Funding", new { branchCode = branchCode });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintChequeVoucher(string documentNo, string branchCode)
        {
            try 
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PrintContract);

                var contractModel = FundingHelper.GetContractModel(documentNo);
                string path = System.Configuration.ConfigurationManager.AppSettings["VoucherPath"].ToString();
                var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + path);

                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    var ws = package.Workbook.Worksheets["VOUCHER"];

                    ws.Cells["A3"].Value = contractModel.BranchName;
                    ws.Cells["A37"].Value = contractModel.BranchName;

                    ws.Cells["A4"].Value = contractModel.BranchAddress;
                    ws.Cells["A38"].Value = contractModel.BranchAddress;

                    ws.Cells["A5"].Value = contractModel.BranchContactNumbers;
                    ws.Cells["A39"].Value = contractModel.BranchContactNumbers;

                    ws.Cells["J6"].Value = "No. " + DateTime.Now.Year.ToString() + "LCS-" + DateTime.Now.Month.ToString();
                    ws.Cells["J40"].Value = "No. " + DateTime.Now.Year.ToString() + "LCS-" + DateTime.Now.Month.ToString();
                    
                    ws.Cells["B8"].Value = contractModel.BorrowerName;
                    ws.Cells["B42"].Value = contractModel.BorrowerName;
                    ws.Cells["I29"].Value = contractModel.BorrowerName;
                    ws.Cells["I63"].Value = contractModel.BorrowerName;

                    ws.Cells["J8"].Value = DateTime.Now.ToString("MMM/dd/yyyy");
                    ws.Cells["J42"].Value = DateTime.Now.ToString("MMM/dd/yyyy");

                    ws.Cells["C12"].Value = contractModel.Term.ToString();
                    ws.Cells["C46"].Value = contractModel.Term.ToString();

                    ws.Cells["E12"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);
                    ws.Cells["E46"].Value = GetFrequency((AdministrationHelper.ProceedType)contractModel.ProceedTypeID);

                    var receivableAmount = contractModel.LoanAmount + contractModel.LoanInterestAmount;

                    ws.Cells["J12"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");
                    ws.Cells["J46"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");

                    ws.Cells["D14"].Value = FundingHelper.ConvertDecimalToWords(receivableAmount.ToString());
                    ws.Cells["D48"].Value = FundingHelper.ConvertDecimalToWords(receivableAmount.ToString());

                    ws.Cells["J15"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");
                    ws.Cells["J49"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");

                    ws.Cells["G18"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");
                    ws.Cells["G52"].Value = "PHP   " + receivableAmount.ToString("#,###,##0.00");

                    ws.Cells["I18"].Value = contractModel.BranchManager;
                    ws.Cells["I52"].Value = contractModel.BranchManager;

                    ws.Cells["G20"].Value = contractModel.LoanInterestAmount.ToString("#,###,##0.00");
                    ws.Cells["G54"].Value = contractModel.LoanInterestAmount.ToString("#,###,##0.00");

                    ws.Cells["B65"].Value = contractModel.ChequeNo.ToUpper();

                    var financeChargeTypes = contractModel.FinanceChargeTypes;
                    var nonFinanceChargeTypes = contractModel.NonFinanceChargeTypes;
                    var deductions = contractModel.Deductions;
                    decimal totalDeductions = 0;

                    var rowStart = 21;
                    var rowStart2 = 55;
                    foreach (var item in financeChargeTypes)
                    {
                        ws.Cells["A" + rowStart.ToString()].Value = item.ChargeTypeName;
                        ws.Cells["A" + rowStart2.ToString()].Value = item.ChargeTypeName;

                        decimal itemDeductionAmount = 0;
                        var itemDeduction = deductions.Where(w => w.ChargeTypeID == item.ID);
                        if (itemDeduction.Any())
                            itemDeductionAmount = itemDeduction.SingleOrDefault<LoanDeduction>().Amount;

                        ws.Cells["G" + rowStart.ToString()].Value = itemDeductionAmount.ToString("#,###,##0.00");
                        ws.Cells["G" + rowStart2.ToString()].Value = itemDeductionAmount.ToString("#,###,##0.00");

                        ++rowStart;
                        ++rowStart2;
                    }

                    foreach (var item in nonFinanceChargeTypes)
                    {
                        ws.Cells["A" + rowStart.ToString()].Value = item.ChargeTypeName;
                        ws.Cells["A" + rowStart2.ToString()].Value = item.ChargeTypeName;

                        decimal itemDeductionAmount = 0;
                        var itemDeduction = deductions.Where(w => w.ChargeTypeID == item.ID);
                        if (itemDeduction.Any())
                            itemDeductionAmount = itemDeduction.SingleOrDefault<LoanDeduction>().Amount;

                        ws.Cells["G" + rowStart.ToString()].Value = itemDeductionAmount.ToString("#,###,##0.00");
                        ws.Cells["G" + rowStart2.ToString()].Value = itemDeductionAmount.ToString("#,###,##0.00");

                        ++rowStart;
                        ++rowStart2;
                    }

                    if (deductions.Any())
                        totalDeductions = deductions.Sum(s => s.Amount);

                    var chequeAmount = receivableAmount - (contractModel.LoanInterestAmount + totalDeductions);

                    ws.Cells["G" + (rowStart + 1).ToString()].Value = chequeAmount.ToString("#,###,##0.00");
                    ws.Cells["G" + (rowStart2 + 1).ToString()].Value = chequeAmount.ToString("#,###,##0.00");

                    string fileName = @"Voucher_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                    string reportsPath = ServerAdminReportsPath + fileName;
                    var newFileInfo = new FileInfo(reportsPath);
                    if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                    package.SaveAs(newFileInfo);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("PrintContract", "Funding", new { branchCode = branchCode });

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult RefreshPrintContract(PrintContractModel model)
        {
            return RedirectToAction("PrintContract", "Funding", new { branchCode = model.SelectedBranchCode, searchText = model.SearchText });
        }

        public ActionResult ReleaseToBranch(string branchCode, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ReleaseToBranch);

                var db = new DatabaseContext();
                var model = FundingHelper.GetReleaseToBranchModel(db, branchCode, searchText, User.Identity.Name);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult ReleaseToBranch(ReleaseToBranchModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ReleaseToBranch);

                using (var db = new DatabaseContext())
                {
                    if (command == AdministrationHelper.DBCommand.Save.ToString() && model.ReleaseToBranchItems != null)
                    {
                        foreach (var item in model.ReleaseToBranchItems)
                        {
                            if (item.IsSelected)
                            {
                                var loan = db.Loans.Where(w => w.DocumentNo == item.DocumentNo).SingleOrDefault<Loan>();
                                loan.StatusID = (int)AdministrationHelper.LoanStatus.ReleasedToBranch;
                                db.Entry(loan).State = System.Data.EntityState.Modified;
                            }
                        }

                        db.SaveChanges();
                    }
                    else
                    {
                        // refreshed
                    }
                }

                return RedirectToAction("ReleaseToBranch", "Funding", new { branchCode = model.SelectedBranchCode, searchText = model.SearchText });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult ReleaseToCustomer(string branchCode, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ReleaseToCustomer);

                var db = new DatabaseContext();
                var model = FundingHelper.GetReleaseToCustomerModel(db, branchCode, searchText, User.Identity.Name);                
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult ReleaseToCustomer(ReleaseToCustomerModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ReleaseToCustomer);

                using (var db = new DatabaseContext())
                {
                    if (command == AdministrationHelper.DBCommand.Save.ToString() && model.ReleaseToCustomerItems != null)
                    {
                        foreach (var item in model.ReleaseToCustomerItems)
                        {
                            if (item.IsSelected)
                            {
                                var loan = db.Loans.Where(w => w.DocumentNo == item.DocumentNo).SingleOrDefault<Loan>();
                                loan.StatusID = (int)AdministrationHelper.LoanStatus.ReleasedToBorrower;
                                loan.DateReleased = item.DateReleased;
                                db.Entry(loan).State = System.Data.EntityState.Modified;
                            }
                        }

                        db.SaveChanges();
                    }
                    else
                    {
                        // refreshed
                    }
                }

                return RedirectToAction("ReleaseToCustomer", "Funding", new { branchCode = model.SelectedBranchCode, searchText = model.SearchText });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult GenerateSchedule(string documentNo, string source)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ReleaseToCustomer);

                var db = new DatabaseContext();
                var model = FundingHelper.GetGenerateScheduleModel(db, documentNo, User.Identity.Name, source);
                return View(model);

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintSchedule(string documentNo)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ReleaseToCustomer);


                return RedirectToAction("ReleaseToCustomer");

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        private decimal GetScheduleAmount(decimal receivableAmount, int term, string proceedType)
        {
            return Math.Ceiling(receivableAmount / term);
            //decimal weeklyDueAmount = receivableAmount / term * 7;
            //decimal dailyDueAmount = weeklyDueAmount / 6;
            //decimal semiMonthlyDueAmount = receivableAmount / term * 15;
            //decimal monthlyDueAmount = receivableAmount / term * 30;

            //switch (proceedType)
            //{
            //    case "Daily": { return Math.Ceiling(dailyDueAmount); }
            //    case "Weekly": { return weeklyDueAmount; }
            //    case "SemiMonthly": { return semiMonthlyDueAmount; }
            //    case "Monthly": { return monthlyDueAmount; }
            //    default: { return Math.Ceiling(dailyDueAmount); }
            //}
        }

        private HtmlTable GetChequeTable(string documentNo)
        {
            var model = FundingHelper.GetLoanQueryModel(documentNo);
            var chequeAmount = model.LoanAmount - model.TotalDeduction;

            var table = ReportHelper.GetTable(setBorder: false);
            
            var row1 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { "" }, setBorder: false, fontSize: 13);
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { DateTime.Now.ToString("MMM dd, yyyy") }, setBorder: false, textAlign: "right", fontSize: 13);

            var row2 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.BorrowerName }, setBorder: false, fontSize: 20);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { chequeAmount.ToString("#,###,##0.00") }, setBorder: false, textAlign: "right", fontSize: 19);

            var row3 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row3, new List<string>() { "*** " + FundingHelper.ConvertDecimalToWords(chequeAmount.ToString()) + " ONLY ***" }, setBorder: false, fontSize: 13);

            return table;
        }

        private string GetFrequency(AdministrationHelper.ProceedType proceedType)
        {
            switch (proceedType)
            {
                case AdministrationHelper.ProceedType.Daily: 
                    {
                        return "days";
                    }
                case AdministrationHelper.ProceedType.Weekly:
                    {
                        return "weeks";
                    }
                case AdministrationHelper.ProceedType.SemiMonthly:
                    {
                        return "semi-months";
                    }
                case AdministrationHelper.ProceedType.Monthly:
                    {
                        return "months";
                    }
                default:
                    {
                        return "-";
                    }
            }
        }
    }
}
